package com.EM1.www.dao;

import com.EM1.www.po.Paper;
import com.EM1.www.util.Connectmysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class ConnectPaper {
    public ArrayList<Paper> selectPapers(int teacherId) throws Exception {
        Connection conn = Connectmysql.Connectmysql();
        String sql="select *from teacher_paper where user_id=?";
        PreparedStatement pmt = conn.prepareStatement(sql);
        String papersql="select *from paper where paper_id=?";
        PreparedStatement paperpmt = conn.prepareStatement(papersql);
        pmt.setInt(1,teacherId);
        ResultSet res = pmt.executeQuery();
        ArrayList<Paper> papers=new ArrayList<>();
        while (res.next()){
            String paperTitle=res.getString("paper_title");
            int paperId= res.getInt("paper_id");
            paperpmt.setInt(1,paperId);
            ResultSet paperres = paperpmt.executeQuery();
            ArrayList<Integer> questionIds=new ArrayList<>();
            while (paperres.next()){
                questionIds.add(paperres.getInt("question_id"));
            }
            Paper paper=new Paper(questionIds,paperTitle,paperId);
            papers.add(paper);
        }
        paperpmt.close();
        pmt.close();
        return papers;
    }
    public void insertpaper(int teacherId,String paperTitle,ArrayList<Integer> questionIds) throws Exception {
        Connection conn = Connectmysql.Connectmysql();
        String questionsql="insert into paper(paper_id, question_id) value (?, ?);";
        PreparedStatement questionpmt = conn.prepareStatement(questionsql);
        String sql="insert into teacher_paper(user_id, paper_title) value (?,?);";
        PreparedStatement pmt=conn.prepareStatement(sql);
        String papersql="select *from teacher_paper where paper_title=?";
        PreparedStatement paperpmt=conn.prepareStatement(papersql);
        pmt.setInt(1,teacherId);
        pmt.setString(2,paperTitle);
        pmt.executeUpdate();
        paperpmt.setString(1,paperTitle);
        ResultSet paperres = paperpmt.executeQuery();
        paperres.next();
        int paper_id = paperres.getInt("paper_id");
        for (Integer questionId : questionIds) {
            questionpmt.setInt(1,paper_id);
            questionpmt.setInt(2,questionId);
            questionpmt.executeUpdate();
        }
        paperpmt.close();
        pmt.close();
        questionpmt.close();
    }
    public Paper selectnewPaper(Paper oldpaper) throws Exception {
        Connection conn = Connectmysql.Connectmysql();
        PreparedStatement pmt = conn.prepareStatement("select *from paper where paper_id=?");
        pmt.setInt(1,oldpaper.getId());
        ResultSet res = pmt.executeQuery();
        Paper paper=new Paper();
        ArrayList<Integer> questions=new ArrayList<>();
        while (res.next()){
            questions.add(res.getInt("question_id"));
        }
        paper.setTitle(oldpaper.getTitle());
        paper.setId(oldpaper.getId());
        paper.setQuestionId(questions);
        return paper;
    }
    public void DeletePaperQuestion(int id,int paperId) throws Exception {
        Connection conn = Connectmysql.Connectmysql();
        PreparedStatement pmt = conn.prepareStatement("delete from paper where question_id=?and paper_id=?");
        pmt.setInt(1,id);
        pmt.setInt(2,paperId);
        pmt.executeUpdate();
        pmt.close();
    }
}
